<?php
/**
 * GioCMS
 * 
 * LICENSE
 *
 * This source file is subject to the GNU GENERAL PUBLIC LICENSE Version 2 
 * that is bundled with this package in the file LICENSE.txt.
 * It is also available through the world-wide-web at this URL:
 * http://www.gnu.org/licenses/gpl-2.0.txt
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to license@ninhgio.com so we can send you a copy immediately.
 * 
 * @copyright	Copyright (c) 2010-2011 GioCMS (http://cms.ninhgio.com)
 * @license		http://www.gnu.org/licenses/gpl-2.0.txt GNU GENERAL PUBLIC LICENSE Version 2
 * @author		NinhGio - ninhgio@gmail.com
 * @since		1.0
 */

class Modules_Ad_Models_Mysql_Banner extends Modules_Ad_Models_Banner
{
	protected $_table = 'ad_banner';
	
	public function getById($id)
	{
		$sql = 'SELECT * FROM ' . $this->_conn->_tablePrefix . 'ad_banner 
							WHERE banner_id = ' . $this->_conn->escape($id) . ' LIMIT 1';
		$rs = $this->_conn->query($sql);
		$return = (0 == mysql_num_rows($rs)) ? null : $this->_conn->fetchAll($rs);
		return $return;
	}
	
	public function loadBanners()
	{
		$sql = "SELECT b.*, pa.zone_id AS banner_zone_id, pa.route, pa.page_url
				FROM " . $this->_conn->_tablePrefix . $this->_table . " AS b
				INNER JOIN " . $this->_conn->_tablePrefix . "ad_page_assoc AS pa
					ON b.banner_id = pa.banner_id
				WHERE b.status = 'active'";
		$rs = $this->_conn->query($sql);
		$banners = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$banners[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $banners;
	}
	
	public function getByPage($pageName = null)
	{
		$sql = 'SELECT b.*, z.width AS zone_width, z.height AS zone_height, z.code AS code FROM ' . $this->_conn->_tablePrefix . 'ad_banner AS b
					INNER JOIN ' . $this->_conn->_tablePrefix . 'ad_page_assoc AS a
						ON b.banner_id = a.banner_id
					INNER JOIN ' . $this->_conn->_tablePrefix . 'ad_zone AS z
						ON a.zone_id = z.zone_id
					INNER JOIN ' . $this->_conn->_tablePrefix . 'core_page AS p
						ON p.page_id = a.page_id
					WHERE z.status = "active" AND b.status = "active"
					ORDER BY a.priority ASC';
		$sql = sprintf($sql, $pageName);
		echo $sql; die();
		$rs = $this->_conn->query($sql);
		$banners = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$row['width'] = ((int)$row['width'] == 0) ? (int)$row['zone_width'] : (int)$row['width'];
			$row['height'] = ((int)$row['height'] == 0) ? (int)$row['zone_height'] : (int)$row['height'];
			$banners[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $banners;
	}
	
	public function getByZonePage($zoneCode, $pageName)
	{
		$sql = 'SELECT b.*, z.width AS width, z.height AS height FROM ' . $this->_conn->_tablePrefix . 'ad_banner AS b
					INNER JOIN ' . $this->_conn->_tablePrefix . 'ad_banner_page_assoc AS a
						ON b.banner_id = a.banner_id
					INNER JOIN ' . $this->_conn->_tablePrefix . 'ad_zone AS z
						ON a.zone_id = z.zone_id
					INNER JOIN ' . $this->_conn->_tablePrefix . 'core_page AS p
						ON p.page_id = a.page_id
					WHERE z.code = "%s" AND p.name = "%s" AND z.status = "active" AND b.status = "active"
					ORDER BY a.priority ASC';
		$sql = sprintf($sql, $zoneCode, $pageName);
		
		$rs = $this->_conn->query($sql);
		$banners = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$banners[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $banners;
	}
	
	public function add($banner)
	{
		return $this->_conn->insert($banner, $this->_conn->_tablePrefix . $this->_table);
	}
	
	public function update($banner)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . $this->_table . ' 
					SET name = "%s", description = "%s", status = "%s",
						file_url = "%s", format = "%s", url = "%s", html_code = "%s", 
						width = "%s", height = "%s", mode = "%s"
					WHERE banner_id = "%d"';
		$sql = sprintf($sql, $this->_conn->escape($banner['name']),
							$this->_conn->escape($banner['description']),
							$this->_conn->escape($banner['status']),
							$this->_conn->escape($banner['file_url']),
							$this->_conn->escape($banner['format']),
							$this->_conn->escape($banner['url']),
							$this->_conn->escape($banner['html_code']),
							$this->_conn->escape($banner['width']),
							$this->_conn->escape($banner['height']),
							$this->_conn->escape($banner['mode']),	
							$this->_conn->escape($banner['banner_id']));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();
	}
	
	public function delete($bannerId)
	{
		$sql = 'DELETE FROM ' . $this->_conn->_tablePrefix . $this->_table . ' 
							WHERE banner_id = "' . $this->_conn->escape($bannerId) . '"';
		$rs = $this->_conn->query($sql);
		$affectRow = $this->_conn->affectedRows();
		$this->_conn->freeResult($rs);
		return $affectRow;
	}
	
	public function find($offset = null, $limit = null, $condition = array())
	{
		$sql = "SELECT b.* FROM " . $this->_conn->_tablePrefix . $this->_table . " AS b";
		if ($condition) {
			$where = array();
			
			if (isset($condition['route']) && $condition['route']) {
				$sql 	.= " INNER JOIN " . $this->_conn->_tablePrefix . "ad_page_assoc AS pa ON b.banner_id = pa.banner_id";
				$where[] = sprintf("pa.route = '%s'", $this->_conn->escape($condition['route']));
			}
			
			if (isset($condition['route']) && $condition['route']
					&& isset($condition['zone_id']) && $condition['zone_id']) 
			{
				$where[] = sprintf("pa.zone_id = '%s'", $this->_conn->escape($condition['zone_id']));
			}
			
			if (isset($condition['banner_id']) && $condition['banner_id']) {
				$where[] = sprintf("b.banner_id = '%s'", $this->_conn->escape($condition['banner_id']));
			}
			if (isset($condition['status']) && $condition['status']) {
				$where[] = sprintf("b.status = '%s'", $this->_conn->escape($condition['status']));
			}
			if (isset($condition['keyword']) && $condition['keyword']) {
				$where[] = "b.name LIKE '%" . addslashes($condition['keyword']) . "%'";
			}
			
			if (count($where) > 0) {
				$sql .= " WHERE " . implode(" AND ", $where);
			}
		}
		$sql .= " ORDER BY b.banner_id DESC";
		if (is_int($offset) && is_int($limit)) {
			$sql .= sprintf(" LIMIT %s, %s", $offset, $limit);
		}
		$rs = $this->_conn->query($sql);
		$users = array();
		while ($row = $this->_conn->fetchAll($rs)) {
			$users[] = $row;
		}
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $users;
	}
	
	public function count($condition = array())
	{
		$sql = 'SELECT COUNT(*) as num_banners FROM ' . $this->_conn->_tablePrefix . $this->_table . ' AS b';
		if ($condition) {
			$where = array();
			
			if (isset($condition['route']) && $condition['route']) {
				$sql 	.= " INNER JOIN " . $this->_conn->_tablePrefix . "ad_page_assoc AS pa ON b.banner_id = pa.banner_id";
				$where[] = sprintf("pa.route = '%s'", $this->_conn->escape($condition['route']));
			}
			
			if (isset($condition['route']) && $condition['route']
					&& isset($condition['zone_id']) && $condition['zone_id']) 
			{
				$where[] = sprintf("pa.zone_id = '%s'", $this->_conn->escape($condition['zone_id']));
			}
			
			if (isset($condition['banner_id']) && $condition['banner_id']) {
				$where[] = sprintf("b.banner_id = '%s'", $this->_conn->escape($condition['banner_id']));
			}
			if (isset($condition['status']) && $condition['status']) {
				$where[] = sprintf("b.status = '%s'", $this->_conn->escape($condition['status']));
			}
			if (isset($condition['keyword']) && $condition['keyword']) {
				$where[] = "b.name LIKE '%" . addslashes($condition['keyword']) . "%'";
			}
			
			if (count($where) > 0) {
				$sql .= " WHERE " . implode(" AND ", $where);
			}
		}
		$rs = $this->_conn->query($sql);
		$row = $this->_conn->fetchAll($rs);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $row['num_banners'];
	}
	
	public function updateStatus($bannerId, $status)
	{
		$sql = 'UPDATE ' . $this->_conn->_tablePrefix . 'ad_banner 
					SET status = "%s" 
					WHERE banner_id = "%s"';
		$sql = sprintf($sql, $this->_conn->escape($status),
							$this->_conn->escape($bannerId));
		$rs = $this->_conn->query($sql);
		/**
		 * Free result 
		 */
		$this->_conn->freeResult($rs);
		return $this->_conn->affectedRows();
	}
}